﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.ComponentModel;
using System.Collections;
using ProgNetComponents.Components;
using ProgNetComponents.Design.Attributes;
using ProgNetComponents.Data.Query;

namespace ProgNetComponents.Data
{
    public abstract class DataManager : CustomTypeDescriptorComponent
    {
        static DataManager()
        {
            DefaultAutoLogExceptions = true;
        }
        public static bool DefaultAutoLogExceptions
        {
            get;
            set;
        }

        protected DataManager()
        {
            AutoLogExceptions = ThreeStateBoolean.Default;
            UseGlobalConnectionString = true;
            AutoCommit = true;
        }

        public Log.Logger Logger
        {
            get;
            set;
        }

        public ThreeStateBoolean AutoLogExceptions
        {
            get;
            set;
        }

        public bool GetAutoLogExceptions
        {
            get { return AutoLogExceptions.GetBool(DefaultAutoLogExceptions); }
        }

        private Dictionary<string, DbDataAdapter> adapters = new Dictionary<string, DbDataAdapter>();
        private DbDataAdapter fillAdapter = null;

        private SchemaChecker _SchemaChecker;
        private string _ConnectionString;
        private Exception __LastException;
        protected Exception _LastException
        {
            get { return __LastException; }
            set
            {
                __LastException = value; 
                if (GetAutoLogExceptions && value != null)
                    value.Log(null, Logger);
            }
        }

        protected DbTransaction transaction;
        private DbConnection _Connection;

        [DynamicBrowsableByProperty("UseGlobalConnectionString", false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Visible)]
        public string ConnectionString
        {
            get
            {
                if (UseGlobalConnectionString)
                    return GetGlobalConnectionString();
                return _ConnectionString;
            }
            set
            {
                if (adapters.Count != 0)
                    adapters.Clear();
                _ConnectionString = value;
            }
        }

        [Browsable(false)]
        public Exception LastException
        {
            get { return _LastException; }
        }

        [DesignerSerializationVisibility(DesignerSerializationVisibility.Visible)]
        public bool UseGlobalConnectionString
        {
            get;
            set;
        }

        public SchemaChecker SchemaChecker
        {
            get
            {
                if (_SchemaChecker == null)
                {
                    _SchemaChecker = CreateSchemaChecker();
                }
                return _SchemaChecker;
            }
        }
        protected DbConnection Connection
        {
            get
            {
                if (_Connection == null)
                    _Connection = CreateConnection(ConnectionString);
                return _Connection;
            }
        }
        public bool HasData
        {
            get { return adapters.Count > 0; }
        }

        public bool AutoCommit
        {
            get;
            set;
        }
        protected abstract SchemaChecker CreateSchemaChecker();
        protected abstract string GetGlobalConnectionString();
        protected abstract DbCommandBuilder CreateCommandBuilder(DbDataAdapter adapter);
        protected abstract DbConnection CreateConnection(string connectionString);
        protected abstract DbDataAdapter CreateAdapter(DbConnection connection, string selectString);
        protected abstract DbParameter CreateParameter(string name, object value);

        public void ClearTableDefs()
        {
            adapters.Clear();
        }
        
        public void AddTableDef(DataTable dt)
        {
            AddTableDef(dt, GetSelectString(dt, dt.TableName));
        }
        public void AddTableDef(DataTable dt, string selectString)
        {
            AddTableDef(dt, dt.TableName, selectString);
        }

        public void AddTableDef(DataTable dt, string dbName, string selectString)
        {
            if (!adapters.ContainsKey(dt.TableName))
            {
                DbDataAdapter ad = CreateAdapter(Connection, GetSelectString(dt, dbName));
                ad.AcceptChangesDuringUpdate = false;

                DbCommandBuilder builder = CreateCommandBuilder(ad);
                ad.InsertCommand = builder.GetInsertCommand();
                ad.DeleteCommand = builder.GetDeleteCommand();
                ad.UpdateCommand = builder.GetUpdateCommand();
                ad.SelectCommand.CommandText = selectString;
                adapters.Add(dt.TableName, ad);
            }
        }
        
        public void AddSelectData(DataSet ds, SelectData data, bool SingleRecord)
        {
            ClearTableDefs();
            
            SelectQueryCollection list = null;
            if (SingleRecord)
                list = data.SingleRecordQueries;
            else
                list = data.MultiRecordQueries;

            foreach (SelectQuery s in list)
            {
                if (ds.Tables.Contains(s.TableDataSetName))
                    AddTableDef(ds.Tables[s.TableDataSetName], s.TableName, s.Query);
            }
        }
        
        private string GetSelectString(DataTable dt, string tableName)
        {
            string str = "SELECT ";
            string cols = "";
            foreach (DataColumn col in dt.Columns)
            {
                if (SchemaChecker.ColumnExists(tableName, col.ColumnName))
                    cols = cols.AppendString(", ") + col.ColumnName;
            }
            if (cols.IsNull())
            {
                List<string> l = SchemaChecker.GetTableColumns(tableName);
                foreach(string s in l)
                    cols = cols.AppendString(", ") + s;
            }
            return str + cols + " FROM " + tableName;
        }
        /// <summary>
        /// Executes the query and returns the first column of the first row in the result
        /// set returned by the query. All other columns and rows are ignored.
        /// </summary>
        /// <param name="query"></param>
        /// <returns>The first column of the first row in the result set.</returns>
        public object ExecuteScalar(string query)
        {
            object obj = null;
            DbCommand cmd = null;
            DbConnection conn = null;
            try
            {
                _LastException = null;
                conn = CreateConnection(ConnectionString);
                cmd = conn.CreateCommand();
                cmd.CommandText = query;
                conn.Open();
                obj = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                _LastException = ex;
            }
            finally
            {
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        try
                        {
                            conn.Close();
                        }
                        catch { }
                    conn.Dispose();
                }
                if (cmd != null)
                    cmd.Dispose();
            }
            return obj;
        }
        /// <summary>
        /// Executes a SQL statement against a connection object.
        /// </summary>
        /// <param name="query"></param>
        /// <returns>The number of rows affected.</returns>
        public int ExecuteNonQuery(string query)
        {
            int obj = -1;
            DbCommand cmd = null;
            DbConnection conn = null;
            try
            {
                _LastException = null;
                conn = CreateConnection(ConnectionString);
                cmd = conn.CreateCommand();
                cmd.CommandText = query;
                conn.Open();
                obj = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                _LastException = ex;
            }
            finally
            {
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        try
                        {
                            conn.Close();
                        }
                        catch { }
                    conn.Dispose();
                }
                if (cmd != null)
                    cmd.Dispose();
            }
            return obj;
        }
        public bool FillTable(DataTable dt, string selectString)
        {
            bool result = true;
            if (fillAdapter == null || fillAdapter.SelectCommand == null || fillAdapter.SelectCommand.CommandText != selectString)
            {
                if(fillAdapter!=null)
                    fillAdapter.Dispose();
                fillAdapter = CreateAdapter(CreateConnection(ConnectionString), selectString);
            }
            try
            {
                dt.Clear();
                fillAdapter.Fill(dt);
                dt.AcceptChanges();
            }
            catch (Exception ex)
            {
                result = false;
                _LastException = ex;
            }
            return result;
        }
        
        public bool FillTable(DataTable dt, params ObjectKeyValuePair[] parameters)
        {
            bool result = true;
            DbDataAdapter ad = null;
            if (adapters.ContainsKey(dt.TableName))
            {
                ad = adapters[dt.TableName];
                try
                {
                    if (parameters != null && parameters.Length > 0)
                    {
                        ad.SelectCommand.Parameters.Clear();
                        foreach (ObjectKeyValuePair kvp in parameters)
                        {
                            if (ad.SelectCommand.CommandText.Contains(kvp.Key))
                                ad.SelectCommand.Parameters.Add(CreateParameter(kvp.Key, kvp.Value));
                        }
                    }
                    dt.Clear();
                    ad.Fill(dt);
                    dt.AcceptChanges();
                }
                catch (Exception ex)
                {
                    result = false;
                    _LastException = ex;
                }
            }
            return result;
        }

        public bool FillDataSet(DataSet ds, params ObjectKeyValuePair[] parameters)
        {
            bool result = true;
            ds.EnforceConstraints = false;
            foreach (DataTable dt in ds.Tables)
            {
                result &= FillTable(dt, parameters);
                if (!result)
                    break;
            }
            try
            {
                ds.EnforceConstraints = true;
            }
            catch (Exception ex)
            {
                _LastException = ex;
                result = false;
            }
            return result;
        }
        
        public bool UpdateTable(DataTable dt)
        {
            return Update(dt.GetRealChangedRows(false));
        }
        public bool Update(DataRow[] rows)
        {
            bool result = true;
            DbDataAdapter ad = null;
            if (rows != null && rows.Length > 0)
            {
                try
                {

                    if (adapters.ContainsKey(rows[0].Table.TableName))
                    {
                        ad = adapters[rows[0].Table.TableName];
                        ad.UpdateCommand.Transaction = transaction;
                        ad.InsertCommand.Transaction = transaction;
                        ad.DeleteCommand.Transaction = transaction;
                    }
                    else
                    {
                        if (fillAdapter != null)
                        {
                            ad = fillAdapter;
                            if (fillAdapter.UpdateCommand == null)
                            {
                                DbCommandBuilder builder = CreateCommandBuilder(fillAdapter);
                                fillAdapter.InsertCommand = builder.GetInsertCommand();
                                fillAdapter.DeleteCommand = builder.GetDeleteCommand();
                                fillAdapter.UpdateCommand = builder.GetUpdateCommand();
                            }
                        }
                    }
                    if (ad != null)
                        ad.Update(rows);
                }
                catch (Exception ex)
                {
                    rows[0].Table.ClearErrors();
                    _LastException = ex;
                    result = false;
                }
            }
            return result;
        }
        public bool UpdateTableInTransaction(DataTable dt)
        {
            if (transaction == null)
                StartTransaction();
            return UpdateTable(dt);
        }
        
        public bool UpdateDataSet(DataSet ds)
        {
            bool result = true;

            foreach (string tab in adapters.Keys)
            {
                if (ds.Tables.Contains(tab))
                {
                    DataTable dt = ds.Tables[tab];
                    result &= UpdateTableInTransaction(dt);
                    if (!result)
                        break;
                    else
                    {
                        foreach (DataRelation rel in ds.Relations)
                        {
                            if (rel.ParentTable == dt)
                            {
                                UpdateRelations(rel);
                            }
                        }
                    }
                }
            }
            if (AutoCommit)
            {
                if (result)
                    Commit();
                else
                    Rollback();
            }
            return result;
        }

        private void UpdateRelations(DataRelation rel)
        {
            DataRow[] rows = rel.ChildTable.Select(null, null, DataViewRowState.Deleted);
            foreach (DataRow r in rows)
                r.AcceptChanges();
            DataSet ds = rel.DataSet;
            if (ds != null)
            {
                foreach (DataRelation r in ds.Relations)
                {
                    if (r.ParentTable == rel.ChildTable)
                    {
                        UpdateRelations(r);
                    }
                }
            }
        }

        public void StartTransaction()
        {
            Connection.Open();
            transaction = Connection.BeginTransaction();
        }

        public void Commit()
        {
            if (transaction != null)
            {
                transaction.Commit();
                transaction.Dispose();
                Connection.Close();
                transaction = null;
            }
        }

        public void Rollback()
        {
            if (transaction != null)
            {
                transaction.Rollback();
                transaction.Dispose();
                Connection.Close();
                transaction = null;
            }
        }

        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
            if (disposing)
            {
                try
                {
                    foreach (DbDataAdapter ad in adapters.Values)
                        ad.Dispose();
                    if (fillAdapter != null)
                        fillAdapter.Dispose();
                }
                catch { }
            }
        }
    }

}
